/*  This file is part of LitSearch.

    LitSearch is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation; either version 2 of the License, or
    (at your option) any later version.

    LitSearch is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with LitSearch; if not, write to the Free Software
    Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA */

package de.fhbrs.litsearch;


import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;

import com.softtech.jdbc.ConnectionPool;
import com.softtech.jdbc.SQLExecutor;
import com.softtech.jdbc.SQLResults;

import de.fhbrs.ConfigReader.ConfigReader;

/**
 * Creates a connection to the database on request and allows SELECT, INSERT, UPDATE and DELETE statements
 *
 * @version 	1.0 6 Jan 2006
 * @author 		Marco Werner, Alexander Schaer, Cheikh Ben Elvagha
 * 
 */
public class JDBCBean {
	private final static String DBDRIVER = "com.mysql.jdbc.Driver";
	private final static int maxRows = 10000000;
	private static String connstr = "";
	private static String con =  "jdbc:mysql://";
	private static String hostname = "";
	private static String port = "";
	private static String database = "";
	private static String username = "";
	private static String password = "";
	private int rowCount, recordsUpdated;
	private SQLResults resultSet;
	
	/** 
     * Set all instance variables to default values and read connection information from flat file
     */
	public JDBCBean() {
		this.recordsUpdated = this.rowCount = 0;
		if (JDBCBean.hostname.length() == 0) readFlatFile();
	}
	
	/** 
     * Get Result of type SQLResults
     * @param query SQL query as a string
     * @return Result set from last query
     */
	public SQLResults getResultSet(String query) {
		this.executeQuery(query);
		return this.resultSet;
	}
	
	/** 
     * Get the number of records affected by the SQL query (only set if query was of type INSERT, UPDATE or DELETE)
     * @return Records updated by last query
     */
	public int getRecordsUpdated() {
		return this.recordsUpdated;
	}
	
	/** 
     * Get the number of rows returned by the SQL query (only set if query was of type SELECT)
     * @return Amount of rows in the active result set
     */
	public int getRowCount() {
		return this.rowCount;
	}
	
	/** 
     * Execute SQL query
     * @param query SQL query as string
     */
	public void executeQuery(String query) {
		if (query.toUpperCase().startsWith("SELECT")) {
			this.executeSelect(query);
		}
		else if (query.toUpperCase().startsWith("INSERT")) {
			this.executeInsert(query);
		}
		else if (query.toUpperCase().startsWith("UPDATE")) {
			this.executeUpdate(query);
		}
		else if (query.toUpperCase().startsWith("DELETE")) {
			this.executeDelete(query);
		}
	}
	
	/** 
     * Execute SELECT query
     * @param query SELECT query as string
     */
	private void executeSelect(String query) {
		ConnectionPool conPool = new ConnectionPool(1, DBDRIVER, connstr, username, password);
		SQLExecutor sqlExec = new SQLExecutor(conPool);
		sqlExec.setMaxRows(JDBCBean.maxRows);
	   	this.resultSet = sqlExec.runQueryCloseCon(query);
	   	this.rowCount = this.resultSet.getRowCount();
	}
		
	/** 
     * Execute INSERT query
     * @param query INSERT query as string
     */
	private void executeInsert(String query) {
		ConnectionPool conPool = new ConnectionPool(1, DBDRIVER, connstr, username, password);
	   	SQLExecutor sqlExec = new SQLExecutor(conPool);
	    sqlExec.runQueryCloseCon(query);
	    this.recordsUpdated = sqlExec.getNumRecordsUpdated(); 
	}
	
	/** 
     * Execute UPDATE query
     * @param query UPDATE query as string
     */
	private void executeUpdate(String query) {
		ConnectionPool conPool = new ConnectionPool(1, DBDRIVER, connstr, username, password);
	   	SQLExecutor sqlExec = new SQLExecutor(conPool);
	    sqlExec.runQueryCloseCon(query);
	    this.recordsUpdated = sqlExec.getNumRecordsUpdated(); 
	}
	
	/** 
     * Execute DELETE query
     * @param query DELETE query as string
     */
	private void executeDelete(String query) {
		ConnectionPool conPool = new ConnectionPool(1, DBDRIVER, connstr, username, password);
	   	SQLExecutor sqlExec = new SQLExecutor(conPool);
	    sqlExec.runQueryCloseCon(query);
	    this.recordsUpdated = sqlExec.getNumRecordsUpdated(); 
	}
	
	/** 
     * Reads username and password for database access from flat files
     */
	private void readFlatFile() {
		JDBCBean.port = ConfigReader.getInstance().readKey("litsearch.global.mysql.port");
		JDBCBean.hostname = ConfigReader.getInstance().readKey("litsearch.global.mysql.host");
		JDBCBean.database = ConfigReader.getInstance().readKey("litsearch.global.mysql.database");
		JDBCBean.username = ConfigReader.getInstance().readKey("litsearch.global.mysql.user");
		JDBCBean.password = ConfigReader.getInstance().readKey("litsearch.global.mysql.pass");
		JDBCBean.connstr = JDBCBean.con + JDBCBean.hostname + ":" + JDBCBean.port + "/" + JDBCBean.database;
	}
	public int getFieldNames(){
		ConnectionPool conPool = new ConnectionPool(1, DBDRIVER, connstr, username, password);
		SQLExecutor sqlExec = new SQLExecutor(conPool);
	   	this.resultSet = sqlExec.runQueryCloseCon("DESC literatur");
	   	this.rowCount = this.resultSet.getRowCount();
	   	return this.rowCount;
	}
}